package org.jeecg.modules.demo.excePlan.service.impl;

import cn.hutool.core.util.ObjectUtil;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.util.DateUtils;
import org.jeecg.common.util.IOFileUtil;
import org.jeecg.modules.demo.excePlan.entity.ExcePlan;
import org.jeecg.modules.demo.excePlan.mapper.ExcePlanMapper;
import org.jeecg.modules.demo.excePlan.service.IExcePlanService;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @Description: exce_plan
 * @Author: jeecg-boot
 * @Date: 2023-12-26
 * @Version: V1.0
 */
@Service
@DS("db1")
public class ExcePlanServiceImpl extends ServiceImpl<ExcePlanMapper, ExcePlan> implements IExcePlanService {
    @Resource
    private ExcePlanMapper excePlanMapper;

    @Override
    public Result<?> importExcelExcePlan(HttpServletRequest request, HttpServletResponse response) {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        List<ExcePlan> excePlanResultList = new ArrayList<>();
        for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
            // 获取上传文件对象
            MultipartFile file = entity.getValue();
            //设置五个需要导入的sheet
            List<String> sheetLists = new ArrayList<>();
            sheetLists.add("中压");
            sheetLists.add("NES-12");
            sheetLists.add("EZ POWER");
            sheetLists.add("低压");
            sheetLists.add("纳图江苏");
            try {
                //创建工作簿
                XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());
                //获取工作簿下sheet的个数 目前excePlan限定是5个
                int sheetNum = xssfWorkbook.getNumberOfSheets();
                //寻了多少个sheet的标记
                int isSheet = 0;

                System.out.println("该excel文件中总共有：" + sheetNum + "个sheet");
                //遍历工作簿中的所有数据
                for (int i = 0; i < sheetNum - 1; i++) {
                    //读取第i个工作表
                    XSSFSheet sheet = xssfWorkbook.getSheetAt(i);
                    //判断是五个之中的sheet，不是跳出循环
                    int noSheet = 0;
                    for (String sheetList : sheetLists) {
                        if (sheetList.equals(sheet.getSheetName())) {
                            isSheet++;
                            break;
                        } else {
                            noSheet++;
                        }
                    }
                    if (noSheet >= sheetLists.size()) {
                        //说明没匹配上
                        continue;
                    }
                    if (isSheet > sheetLists.size()) {
                        //说明全部匹配过了
                        break;
                    }

                    System.out.println("读取 " + sheet.getSheetName() + " sheet");
                    //获取最后一行的num，即总行数。此处从0开始
                    int maxRow = sheet.getLastRowNum();
                    for (int row = 4; row <= maxRow; row++) {
                        //获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***
                        int maxRol = sheet.getRow(row).getLastCellNum();
                        System.out.println("--------第" + (row+1) + "行的数据如下--------");
                        //每行创建一个新的对象进行封装数据
                        ExcePlan excePlan = new ExcePlan();
                        for (int rol = 0; rol < maxRol; rol++) {
                            XSSFCell cell = sheet.getRow(row).getCell(rol);
                            //如果是合同号为空那就这行数据就没必要解析了
                            if (rol == 3 && StringUtils.isBlank(cell.toString())) {
                                break;
                            }
                            excePlan = genExcePlan(excePlan, cell, rol);
                            //判断是那个sheet来源
                            excePlan.setSheetSource(iSSheetName(sheetLists, sheet));
                        }
                        //如果是合同号为空的数据默认数据为空或非法不录入
                        if (StringUtils.isBlank(excePlan.getContractNumber())) {
                            //如果合同号为空说明这行数据没用
                            continue;
                        }
                        excePlanResultList.add(excePlan);
                    }
                }
                //录入数据库
                System.out.println(excePlanResultList);
                boolean b = saveBatch(excePlanResultList);
                if (b == false) {
                    return Result.error("文件导入失败！");
                }

            } catch (IOException e) {
                e.printStackTrace();
                System.out.println("导入失败！");
            }
        }
        return Result.ok("文件导入成功！");
    }


    /**
     * 验证Excel正确性
     */
    @Override
    public Result<T> verifyExcel(HttpServletRequest request, HttpServletResponse response) {
        //解析Excel
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        List<String> errMessageList = new ArrayList<>();
        for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
            // 获取上传文件对象
            MultipartFile file = entity.getValue();
            //设置五个需要导入的sheet
            List<String> sheetLists = new ArrayList<>();
            sheetLists.add("中压");
            sheetLists.add("NES-12");
            sheetLists.add("EZ POWER");
            sheetLists.add("低压");
            /*sheetLists.add("纳图江苏");*/
            try {
                //创建工作簿
                XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());
                //获取工作簿下sheet的个数 目前excePlan限定是5个
                int sheetNum = xssfWorkbook.getNumberOfSheets();
                //寻了多少个sheet的标记
                int isSheet = 0;

                System.out.println("该excel文件中总共有：" + sheetNum + "个sheet");
                //遍历工作簿中的所有数据
                for (int i = 0; i < sheetNum - 1; i++) {
                    //读取第i个工作表
                    XSSFSheet sheet = xssfWorkbook.getSheetAt(i);
                    //判断是五个之中的sheet，不是跳出循环
                    int noSheet = 0;
                    for (String sheetList : sheetLists) {
                        if (sheetList.equals(sheet.getSheetName())) {
                            isSheet++;
                            break;
                        } else {
                            noSheet++;
                        }
                    }
                    if (noSheet >= sheetLists.size()) {
                        //说明没匹配上
                        continue;
                    }
                    if (isSheet > sheetLists.size()) {
                        //说明全部匹配过了
                        break;
                    }

                    System.out.println("读取 " + sheet.getSheetName() + " sheet");
                    //获取最后一行的num，即总行数。此处从0开始
                    int maxRow = sheet.getLastRowNum();
                    for (int row = 4; row <= maxRow; row++) {
                        //获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***
                        int maxRol = sheet.getRow(row).getLastCellNum();
                        System.out.println("--------第" + (row+1) + "行的数据如下--------");
                        //每行创建一个新的对象进行封装数据
                        ExcePlan excePlan = new ExcePlan();
                        for (int rol = 0; rol < maxRol; rol++) {
                            XSSFCell cell = sheet.getRow(row).getCell(rol);
                            //如果是合同号为空那就这行数据就没必要解析了
                            if (rol == 3 && StringUtils.isBlank(cell.toString())) {
                                break;
                            }
                            String errMessage = verifyCell(cell, rol);
                            if (StringUtils.isNotEmpty(errMessage)) {
                                errMessage = sheet.getSheetName()+"表  第"+(row+1)+"行 "+errMessage;
                                errMessageList.add(errMessage);
                            }
                        }
                        //如果是合同号为空的数据默认数据为空或非法不录入
                        if (StringUtils.isBlank(excePlan.getContractNumber())) {
                            //如果合同号为空说明这行数据没用
                            continue;
                        }
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
                System.out.println("导入失败！");
            }
        }
        if (errMessageList.size() <= 0 || errMessageList.isEmpty()) {
            return Result.ok("excel验证通过，未检测到错误！");
        } else {
            //去桌面生成一个错误报告文本
            //输出txt文本
            String genFilePath = "C:\\Users\\chenpengfei\\Desktop\\errExcel.txt";
            int iOResult = IOFileUtil.genTxtFile(genFilePath, errMessageList);
            if (iOResult>0) {
                return Result.error("excel有误！请查看桌面上errExcel文件内详情");
            } else {
                return Result.error("验证结果未生成成功！");
            }

        }
    }

    /**
     * 获取单元格颜色的方法
     * 无色：0,红色：1,绿色：2,黄色：3,橙色: 4
     */
    private int getCellColor(XSSFCell cell) {
        //获取颜色对象xssfColor
        XSSFColor fillForegroundColorColor = cell.getCellStyle().getFillForegroundColorColor();
        byte[] rgb;
        if (ObjectUtil.isNotEmpty(fillForegroundColorColor)) {
            //获取十六进制颜色代码
            rgb = fillForegroundColorColor.getRgb();
            String color = String.format("#%02X%02X%02X", rgb[0], rgb[1], rgb[2]);
            if ("#FFFFFF".equals(color)) {
                return 0;
            } else if ("#00FF00".equals(color)) {
                return 2;
            } else if ("#FFFF00".equals(color)) {
                return 3;
            } else if ("#FF0000".equals(color)){
                return 1;
            } else if ("#FFC000".equals(color)) {
                return 4;
            } else {
                return 0;
            }
        } else {
            return 0;
        }
    }

    /**
     * 切割日期字符串
     */
    private String qieDateString(String date) {
        return null;
    }

    /**
     * 判断sheet的名称
     */
    private String iSSheetName(List<String> sheetLists, XSSFSheet sheet) {
        String sheetName = sheet.getSheetName();
        for (int i = 0; i < sheetLists.size(); i++) {
            if (sheetLists.get(i).equals(sheetName)) {
                return i + "";
            }
        }
        return "-1";
    }


    /**
     * 组装对象的方法
     */
    private ExcePlan genExcePlan(ExcePlan excePlan, XSSFCell cell, int rol) {

        if (rol == 0) {
            if (StringUtils.isBlank(cell.toString())) {
                return excePlan;
            }
            excePlan.setNoId((int) Double.parseDouble(cell.toString()));
        } else if (rol == 1) {
            excePlan.setIssDate(DateUtils.SBChineseDate(cell.toString()));
        } else if (rol == 2) {
            excePlan.setIsMatch(cell.toString());
        } else if (rol == 3) {
            excePlan.setContractNumber(cell.toString());
        } else if (rol == 4) {
            excePlan.setSeller(cell.toString());
        } else if (rol == 5) {
            excePlan.setCustomer(cell.toString());
        } else if (rol == 6) {
            excePlan.setProjectName(cell.toString());
        } else if (rol == 7) {
            excePlan.setContractContent(cell.toString());
        } else if (rol == 8) {
            excePlan.setProjectType(cell.toString());
        } else if (rol == 9) {
            if (StringUtils.isNotEmpty(cell.toString())) {
                excePlan.setProductNum((int) Double.parseDouble(cell.toString()));
            }
        } else if (rol == 10) {
            excePlan.setConcerns(cell.toString());
        } else if (rol == 11) {
            String s = DateUtils.SByMdDate(cell.toString());
            excePlan.setPlanEndTime(s);
        } else if (rol == 12) {
            String s = DateUtils.SByMdDate(cell.toString());
            if ("待排产".equals(s)) {
                excePlan.setProductAfterTime(null);
            }
            excePlan.setProductAfterTime(s);
        } else if (rol == 13) {
            String s = DateUtils.SByMdDate(cell.toString());
            if ("待甲供".equals(s) || "待定".equals(s) || "待验收".equals(s) || "/".equals(s) || "\\".equals(s) || "待验证".equals(s) || "验收".equals(s) || "".equals(s) || StringUtils.isBlank(s)) {
                excePlan.setDateAdjustAfterTime(null);
            } else {
                DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                LocalDate localDate = LocalDate.parse(s, dateTimeFormatter);
                LocalDateTime dateAdjustAfterTime = localDate.atStartOfDay();
                excePlan.setDateAdjustAfterTime(dateAdjustAfterTime);
            }
            excePlan.setAdjustAfterTime(s);
        } else if (rol == 14) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setSendTime(s);
        } else if (rol == 15) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setLongGoodsTime(s);
        } else if (rol == 16) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setConsumerList(s);
        } else if (rol == 17) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setOneMaterList(s);
        } else if (rol == 18) {
            String s = DateUtils.SBChineseDate(cell.toString());
            int cellColor = getCellColor(cell);
            excePlan.setComTwoMater(cellColor);
            excePlan.setTwoMaterList(s);
        } else if (rol == 19) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setSendFileTime(s);
        } else if (rol == 20) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setCabinetFileHandover(s);
        } else if (rol == 21) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setIngredientsTime(s);
        } else if (rol == 22) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setPanelDrawing(s);
        } else if (rol == 23) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setSingleLineDrawing(s);
        } else if (rol == 24) {
            String s = DateUtils.SBChineseDate(cell.toString());
            int cellColor = getCellColor(cell);
            excePlan.setComSchemDrawing(cellColor);
            excePlan.setSchemDrawing(s);
        } else if (rol == 25) {
            String s = DateUtils.SBChineseDate(cell.toString());
            int cellColor = getCellColor(cell);
            excePlan.setComSelfBom(cellColor);
            excePlan.setSelfRestraintBom(s);
        } else if (rol == 26) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setOutsourcePart(s);
        } else if (rol == 27) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setCopperProductTime(s);
        } else if (rol == 28) {
            String s = DateUtils.SBChineseDate(cell.toString());
            int cellColor = getCellColor(cell);
            excePlan.setComCopperCableBom(cellColor);
            excePlan.setCopperCableBom(s);
        } else if (rol == 29) {
            excePlan.setEleEngineer(cell.toString());
        } else if (rol == 30) {
            excePlan.setStruEngineer(cell.toString());
        } else if (rol == 31) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setCabArriveTime(s);
        } else if (rol == 32) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setEleDeviceTime(s);
        } else if (rol == 33) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setGasTankTime(s);
        } else if (rol == 34) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setGasReallyTime(s);
        } else if (rol == 35) {
            excePlan.setExceInfo(cell.toString());
        } else if (rol == 36) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setGilding(s);
        } else if (rol == 37) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setSpray(s);
        } else if (rol == 38) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setCopperProcess(s);
        } else if (rol == 39) {
            String s = DateUtils.SBChineseDate(cell.toString());
            excePlan.setUpTin(s);
        } else if (rol == 40) {
            excePlan.setMaterialProp(cell.toString());
        } else if (rol == 41) {
            excePlan.setPackageType(cell.toString());
        }
        return excePlan;
    }

    /**
     *验证excel单元格内数据正确性
     */
    private String verifyCell( XSSFCell cell, int rol) {
        try {
            if (rol == 9) {
                if (StringUtils.isNotEmpty(cell.toString().trim())) {
                    int tempI = (int) Double.parseDouble(cell.toString());
                }
            } else if (rol==13){
                String s = DateUtils.SByMdDate(cell.toString());
                if ("待甲供".equals(s) || "待定".equals(s) || "待验收".equals(s) || "/".equals(s) || "\\".equals(s) || "待验证".equals(s) || "验收".equals(s) || "".equals(s) || StringUtils.isBlank(s)) {
                    return null;
                } else {
                    DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                    LocalDate localDate = LocalDate.parse(s, dateTimeFormatter);
                    LocalDateTime dateAdjustAfterTime = localDate.atStartOfDay();
                }
            } else if (rol == 14) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 15) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 16) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 17) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 18) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 19) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 20) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 21) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 22) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 23) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 24) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 25) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 26) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 27) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 28) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 31) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 32) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 33) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 34) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 36) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 37) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 38) {
                DateUtils.SBChineseDate(cell.toString());
            } else if (rol == 39) {
                DateUtils.SBChineseDate(cell.toString());
            }
        } catch (Exception e) {
            return "第"+(rol+1)+"列,  值： "+cell.toString()+"  有问题！";
        }
        return null;
    }
}
